package com.ibs.hadoop.spring.hive.impl;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.ibs.hadoop.spring.hive.HiveJdbcAttachDaoInf;

/**
 * 角色信息数据加载
 * 
 * @author liujun
 * 
 * @vsersion 0.0.1
 */
@Repository
public class HiveJdbcAttachDaoImpl implements HiveJdbcAttachDaoInf {

    /**
     * 数据库操作
     */
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void attachQuery() {
        String sql = "select 'GET' TYPE,        t.TTIME,      " + "  null ENB_ID,        null TAC,        CI,       "
                + " S_GW_IP,        S_GW,        IP_HOME,        RAT_TYPE,    "
                + "    DEST_IP,        DEST_PORT,        null CAUSE_OPINION,      "
                + "  OS,        CLIENTSOFT,        TOTAL_CATEGORY ACCESS_TYPE,   "
                + "     SUB_CATEGORY1 CLASS1,        SUB_CATEGORY2 DOMAIN1,       "
                + " SUB_CATEGORY3 DOMAIN2,        SUB_CATEGORY4 CLASS2,        CATEGORY,  "
                + "      CLIENTSOFT2,        TITLE,        KEYWORDS,     "
                + "   null DNSSERVER_IPADDR,        null DOMIAN_NAME,        "
                + "null RETURN_IPADDR,        null RETURNCODE,        CONTENT_INDEX,  "
                + "      SEARCH_WORDS,        CONTENT_LENGTH,     "
                + "   WEB_VOLUME HTTP_LENGTH,        ANSWER_TIME,    "
                + "    RESPONSE_TIME DELAY,        null TIMEOUT,        case    "
                + "      when RESPONSE_TIME > 0 then          " + " round(CONTENT_LENGTH / RESPONSE_TIME, 2)       "
                + " end SPEED,        case          when PACKETS > 0 then      "
                + "     round(RETRANSFER_COUNT / PACKETS, 2)        end RETRANSFER_RATE,    "
                + "    URI_MAIN,        URI,        USER_AGENT,        CONTENT_TYPE,      "
                + "  LONGITUDE,        LATITUDE,        STATUS_CODE,        ENB_IP,      "
                + "  APN,        ENB,        null MME_IP,        null MME,        "
                + "null M_TMSI,        null MMECODE,        null NEW_M_TMSI,       "
                + " null NEW_MMECODE,        FAILURE_CAUSE FAILURE_CAUSE,        IMSI,   "
                + "     IMEI,        t.MOBILE_TYPE,        MOBILE_NAME,        MOBILE_BRAND,   "
                + "     null MP_PRICE,        null OS_IMEI,        null PRODUCE_DATE,     "
                + "   null FAILURE_MSG,        null DIRECTION,        null UL_TEID,      "
                + "  null S_GW_IP_S1,        null TMSI_CS,        USER_IP_VALUE USER_IP,  "
                + "      null SUBJECT,        null FROM_NUMBER,        null TO_NUMBER,      "
                + "  null RAND,        null TTL,        SUCCESS,        null AUTHREQ_DELAY, "
                + "       null AUTHRES_DELAY,        null DELAY_CN,        null DELAY_RADIO, "
                + "       RECORD_FILE_INDEX,        null LAST_PACKETS_DELAY,        LAST_FILEINDEX, "
                + "       FRAME,        null TAU_TYPE,        LAST_FRAME,        null KNASENC1,  "
                + "      null KNASENC2,        null ATTACH_TYPE   from (select                "
                + "   t.datamsg.TTIME             as TTIME,          t.datamsg.CI              "
                + "  as CI,          t.datamsg.S_GW_IP           as S_GW_IP,          t.datamsg.S_GW           "
                + "   as S_GW,          t.datamsg.IP_HOME           as IP_HOME,          t.datamsg.RAT_TYPE       "
                + "   as RAT_TYPE,          t.datamsg.DEST_IP           as DEST_IP,          t.datamsg.DEST_PORT        "
                + " as DEST_PORT,          t.datamsg.OS                as OS,          t.datamsg.CLIENTSOFT        as CLIENTSOFT,     "
                + "     t.datamsg.TOTAL_CATEGORY    as TOTAL_CATEGORY,          t.datamsg.SUB_CATEGORY1     as SUB_CATEGORY1,        "
                + "  t.datamsg.SUB_CATEGORY2     as SUB_CATEGORY2,          t.datamsg.SUB_CATEGORY3     as SUB_CATEGORY3,      "
                + "    t.datamsg.SUB_CATEGORY4     as SUB_CATEGORY4,          t.datamsg.CATEGORY          as CATEGORY,         "
                + " t.datamsg.CLIENTSOFT2       as CLIENTSOFT2,          t.datamsg.TITLE             as TITLE,          t.datamsg.KEYWORDS      "
                + "    as KEYWORDS,          t.datamsg.CONTENT_INDEX     as CONTENT_INDEX,          t.datamsg.SEARCH_WORDS      as SEARCH_WORDS,       "
                + "   t.datamsg.CONTENT_LENGTH    as CONTENT_LENGTH,          t.datamsg.WEB_VOLUME        as WEB_VOLUME,          t.datamsg.ANSWER_TIME "
                + "      as ANSWER_TIME,          t.datamsg.RESPONSE_TIME     as RESPONSE_TIME,          t.datamsg.PACKETS           as PACKETS,     "
                + "     t.datamsg.RETRANSFER_COUNT  as RETRANSFER_COUNT,          t.datamsg.URI_MAIN          as URI_MAIN,          t.datamsg.URI     "
                + "          as URI,          t.datamsg.USER_AGENT        as USER_AGENT,          t.datamsg.CONTENT_TYPE      as CONTENT_TYPE,      "
                + "    t.datamsg.LONGITUDE         as LONGITUDE,          t.datamsg.LATITUDE          as LATITUDE,          t.datamsg.STATUS_CODE   "
                + "    as STATUS_CODE,          t.datamsg.ENB_IP            as ENB_IP,          t.datamsg.APN               as APN,          t.datamsg.ENB "
                + "              as ENB,          t.datamsg.FAILURE_CAUSE     as FAILURE_CAUSE,          t.datamsg.IMSI              as IMSI,       "
                + "   t.datamsg.IMEI              as IMEI,          t.datamsg.MOBILE_TYPE       as MOBILE_TYPE,          j0.MOBILE_NAME            "
                + "  as MOBILE_NAME,          j0.MOBILE_BRAND             as MOBILE_BRAND,          t.datamsg.USER_IP_VALUE     as USER_IP_VALUE,       "
                + "   t.datamsg.SUCCESS           as SUCCESS,          t.datamsg.RECORD_FILE_INDEX as RECORD_FILE_INDEX,          t.datamsg.LAST_FILEINDEX  "
                + "  as LAST_FILEINDEX,          t.datamsg.FRAME             as FRAME,          t.datamsg.LAST_FRAME        as LAST_FRAME         "
                + "  from lte_get_hb2_160829 t           left join SY_TERMINAL j0             on t.datamsg.mobile_type = j0.MOBILE_TYPE         "
                + " where t.datamsg.TTIME >= TIMESTAMP'2016-05-08 08:00:00'          "
                + "  and t.datamsg.TTIME < TIMESTAMP'2016-05-08 09:00:00') t limit 100";

        List<String> query = new ArrayList<>();
        jdbcTemplate.query(sql, new RowMapper<String>() {

            @Override
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {

                System.out.println("int num :" + rowNum);

                ResultSetMetaData data = rs.getMetaData();

                int coloumLength = data.getColumnCount();

                for (int i = 1; i <= coloumLength; i++) {
                    String columnName = data.getColumnName(i);
                    String columnValue = rs.getString(i);

                    System.out.println("列名:" + columnName + ",列值 :" + columnValue);

                    query.add(columnName + ":" + columnValue);
                }

                System.out.println();

                return null;
            }

        });

        query.forEach(s -> {
            System.out.print(s + ".");
            System.out.println();
        });
    }

}
